package csc.fresher.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import csc.fresher.dao.AccountHasInterestRateDao;
import csc.fresher.entity.AccountHasInterestRate;
import csc.fresher.entity.AccountHasInterestRateState;
import csc.fresher.entity.SavingAccountHasInterestRate;
import csc.fresher.entity.SavingInterestRate;
import csc.fresher.utils.DBUtils;
import csc.fresher.utils.DatabaseUtil;

@Repository
public class SavingAccountHasInterestRateDaoImpl implements
		AccountHasInterestRateDao {

	private static final Logger logger = Logger
			.getLogger(PeriodTypeDaoImpl.class);

	@Autowired
	DataSource dataSource;

	@Override
	public long insert(AccountHasInterestRate accountHasInterestRate) {
		String query = "INSERT INTO `SavingAccount_has_InterestRate`(`SavingAccount_AccNumber`,`InterestRate_InterestRateID`,`FromDate`, `ToDate`, `Balance`, `State`) VALUES (?,?,?,?,?,?)";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setLong(1, accountHasInterestRate.getAccountNumber());
			preparedStatement.setInt(2, accountHasInterestRate.getInterestRateID());
			preparedStatement.setTimestamp(3, accountHasInterestRate.getFromDate());
			preparedStatement.setTimestamp(4, accountHasInterestRate.getToDate());
			preparedStatement.setDouble(5, accountHasInterestRate.getBalance());
			preparedStatement.setString(6, accountHasInterestRate.getState().getState());
			preparedStatement.execute();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			if(rs.next()) {
				return rs.getLong(1);
			}
			
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeConnection(connection, preparedStatement, logger);
		}
		return 0;
	}

	public boolean changeInterestRate(AccountHasInterestRate oldRate, AccountHasInterestRate newRate){
		String query = "UPDATE `SavingAccount_has_InterestRate` SET `State` = ?   WHERE `SavingAccount_has_InterestRate`.`id` = ? ";
		String query2 = "INSERT INTO `SavingAccount_has_InterestRate`(`SavingAccount_AccNumber`,`InterestRate_InterestRateID`,`FromDate`, `ToDate`, `Balance`, `State`) VALUES (?,?,?,?,?,?)";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		PreparedStatement preparedStatement2 = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, oldRate.getState().getState());
			preparedStatement.setLong(2, oldRate.getId());
			if(!preparedStatement.execute()){
				connection.rollback();
				return false;
			}
			
			long newID = 0;
			preparedStatement2 = connection.prepareStatement(query2, Statement.RETURN_GENERATED_KEYS);
			preparedStatement2.setLong(1, newRate.getAccountNumber());
			preparedStatement2.setInt(2, newRate.getInterestRateID());
			preparedStatement2.setTimestamp(3, newRate.getFromDate());
			preparedStatement2.setTimestamp(4, newRate.getToDate());
			preparedStatement2.setDouble(5, newRate.getBalance());
			preparedStatement2.setString(6, newRate.getState().getState());
			preparedStatement2.execute();
			ResultSet rs = preparedStatement2.getGeneratedKeys();
			if(rs.next()) {
				newID = rs.getLong(1);
			}
			
			if(newID <= 0){
				connection.rollback();
				return false;
			}
			return true;
		} catch (SQLException e) {
			logger.error(e);
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			DatabaseUtil.closeConnection(connection, preparedStatement, logger);
		}
		return false;
	}
	
	@Override
	public void update(AccountHasInterestRate accountHasInterestRate) {
		String query = "UPDATE `SavingAccount_has_InterestRate` SET `SavingAccount_has_InterestRate`.`FromDate` = ?, SET `SavingAccount_has_InterestRate`.`ToDate`=?, SET `SavingAccount_has_InterestRate`.`Balance`=? WHERE `SavingAccount_has_InterestRate`.`SavingAccount_AccNumber` = ? `SavingAccount_has_InterestRate`.`InterestRate_InterestRateID` = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			DatabaseUtil.executeUpdate(
					preparedStatement,
					connection,
					query,
					new Object[] { accountHasInterestRate.getFromDate(), accountHasInterestRate.getToDate(), accountHasInterestRate.getBalance(), accountHasInterestRate.getAccountNumber(), accountHasInterestRate.getInterestRateID()});
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeConnection(connection, preparedStatement, logger);
		}
	}

	//not completed
	@Override
	public AccountHasInterestRate getAccountHasInterestRate(long accountNumberID) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<AccountHasInterestRate> getAllAccountHasInterestRates(
			long accountNumberID) {
		List<AccountHasInterestRate> accountHasInterestRates = new ArrayList<AccountHasInterestRate>();
		//String query = "SELECT * FROM `SavingAccount_has_InterestRate` WHERE `SavingAccount_has_InterestRate`.`SavingAccount_AccNumber`=? ORDER BY `FromDate` DESC";
		String query = "SELECT SavingInterestRateID, si.`createdDate`, CurrencyID, PeriodTypeID, Rate,shi.`id`, shi.`State`, si.`FromDate` AS siFromDate,  si.`ToDate` AS siToDate, shi.* "
				+ " FROM `savingaccount` sa LEFT JOIN `savingaccount_has_interestrate` shi"
				+ " ON sa.`SavingAccountNumber` = shi.`SavingAccount_AccNumber`"
				+ " LEFT JOIN `savinginterestrate` si"
				+ " ON si.`SavingInterestRateID` = shi.`InterestRate_InterestRateID`"
				+ " WHERE sa.`SavingAccountNumber`= ? "
				+ " ORDER BY shi.`FromDate` DESC, shi.`id` DESC ";
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, new Object[]{accountNumberID});
			SavingInterestRate savingInterestRate = null;
			SavingAccountHasInterestRate savingAccountHasInterestRate = null;
			while(rs.next()){
				savingInterestRate = new SavingInterestRate();
				savingInterestRate.setCreatedDate(rs.getDate("createdDate"));
				savingInterestRate.setCurrencyID(rs.getInt("CurrencyID"));
				savingInterestRate.setFromDate(rs.getDate("siFromDate"));
				savingInterestRate.setInterestRateID(rs.getInt("SavingInterestRateID"));
				savingInterestRate.setPeriodTypeID(rs.getInt("PeriodTypeID"));
				savingInterestRate.setRate(rs.getDouble("Rate"));
				savingInterestRate.setToDate(rs.getDate("siToDate"));

				savingAccountHasInterestRate = new SavingAccountHasInterestRate();
				savingAccountHasInterestRate.setId(rs.getLong("id"));
				savingAccountHasInterestRate.setAccountNumber(rs.getLong("SavingAccount_AccNumber"));
				savingAccountHasInterestRate.setBalance(rs.getDouble("Balance"));
				savingAccountHasInterestRate.setFromDate(rs.getTimestamp("FromDate"));
				savingAccountHasInterestRate.setToDate(rs.getTimestamp("ToDate"));
				savingAccountHasInterestRate.setInterestRateID(rs.getInt("SavingInterestRateID"));
				savingAccountHasInterestRate.setState(AccountHasInterestRateState.getFromState(rs.getString("State")));
				savingAccountHasInterestRate.setInterestRate(savingInterestRate);
				
				if(savingAccountHasInterestRate.getId() > 0){
					accountHasInterestRates.add(savingAccountHasInterestRate);
				}
				
			}
			
			return accountHasInterestRates;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeResultSetConnection(connection,
					preparedStatement, rs, logger);
		}
		return accountHasInterestRates;
	}

	@Override
	public SavingAccountHasInterestRate getInterestRateByAccountNumberID(long accountNumberID) {
		String query = "SELECT SavingInterestRateID, si.`createdDate`, CurrencyID, PeriodTypeID, Rate,shi.`id`, shi.`State`, si.`FromDate` AS siFromDate,  si.`ToDate` AS siToDate, shi.* "
				+ " FROM `savingaccount` sa LEFT JOIN `savingaccount_has_interestrate` shi"
				+ " ON sa.`SavingAccountNumber` = shi.`SavingAccount_AccNumber`"
				+ " LEFT JOIN `savinginterestrate` si"
				+ " ON si.`SavingInterestRateID` = shi.`InterestRate_InterestRateID`"
				+ " WHERE sa.`SavingAccountNumber`= ? AND shi.`State` = '" + AccountHasInterestRateState.ACCEPTED.getState() + "'"
				+ " ORDER BY shi.`FromDate` DESC LIMIT 1";
		SavingAccountHasInterestRate savingAccountHasInterestRate = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, accountNumberID);
			rs = preparedStatement.executeQuery();
			if (rs.next()){
				SavingInterestRate savingInterestRate = new SavingInterestRate();
				savingInterestRate.setCreatedDate(rs.getDate("createdDate"));
				savingInterestRate.setCurrencyID(rs.getInt("CurrencyID"));
				savingInterestRate.setFromDate(rs.getDate("siFromDate"));
				savingInterestRate.setInterestRateID(rs.getInt("SavingInterestRateID"));
				savingInterestRate.setPeriodTypeID(rs.getInt("PeriodTypeID"));
				savingInterestRate.setRate(rs.getDouble("Rate"));
				savingInterestRate.setToDate(rs.getDate("siToDate"));
				
				savingAccountHasInterestRate = new SavingAccountHasInterestRate();
				savingAccountHasInterestRate.setId(rs.getLong("id"));
				savingAccountHasInterestRate.setAccountNumber(rs.getLong("SavingAccount_AccNumber"));
				savingAccountHasInterestRate.setBalance(rs.getDouble("Balance"));
				savingAccountHasInterestRate.setFromDate(rs.getTimestamp("FromDate"));
				savingAccountHasInterestRate.setToDate(rs.getTimestamp("ToDate"));
				savingAccountHasInterestRate.setInterestRateID(rs.getInt("SavingInterestRateID"));
				savingAccountHasInterestRate.setState(AccountHasInterestRateState.getFromState(rs.getString("State")));
				savingAccountHasInterestRate.setInterestRate(savingInterestRate);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs,
					logger);
		}
		return savingAccountHasInterestRate;
	}
	
	@Override
	public SavingAccountHasInterestRate getNewestInterestRateByAccountNumberID(long accountNumberID) {
		String query = "SELECT SavingInterestRateID, si.`createdDate`, CurrencyID, PeriodTypeID, Rate,shi.`id`, shi.`State`, si.`FromDate` AS siFromDate,  si.`ToDate` AS siToDate, shi.* "
				+ " FROM `savingaccount` sa LEFT JOIN `savingaccount_has_interestrate` shi"
				+ " ON sa.`SavingAccountNumber` = shi.`SavingAccount_AccNumber`"
				+ " LEFT JOIN `savinginterestrate` si"
				+ " ON si.`SavingInterestRateID` = shi.`InterestRate_InterestRateID`"
				+ " WHERE sa.`SavingAccountNumber`= ? "
				+ " ORDER BY shi.`FromDate` DESC LIMIT 1";
		SavingAccountHasInterestRate savingAccountHasInterestRate = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, accountNumberID);
			rs = preparedStatement.executeQuery();
			if (rs.next()){
				SavingInterestRate savingInterestRate = new SavingInterestRate();
				savingInterestRate.setCreatedDate(rs.getDate("createdDate"));
				savingInterestRate.setCurrencyID(rs.getInt("CurrencyID"));
				savingInterestRate.setFromDate(rs.getDate("siFromDate"));
				savingInterestRate.setInterestRateID(rs.getInt("SavingInterestRateID"));
				savingInterestRate.setPeriodTypeID(rs.getInt("PeriodTypeID"));
				savingInterestRate.setRate(rs.getDouble("Rate"));
				savingInterestRate.setToDate(rs.getDate("siToDate"));
				
				savingAccountHasInterestRate = new SavingAccountHasInterestRate();
				savingAccountHasInterestRate.setId(rs.getLong("id"));
				savingAccountHasInterestRate.setAccountNumber(rs.getLong("SavingAccount_AccNumber"));
				savingAccountHasInterestRate.setBalance(rs.getDouble("Balance"));
				savingAccountHasInterestRate.setFromDate(rs.getTimestamp("FromDate"));
				savingAccountHasInterestRate.setToDate(rs.getTimestamp("ToDate"));
				savingAccountHasInterestRate.setInterestRateID(rs.getInt("SavingInterestRateID"));
				savingAccountHasInterestRate.setState(AccountHasInterestRateState.getFromState(rs.getString("State")));
				savingAccountHasInterestRate.setInterestRate(savingInterestRate);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs,
					logger);
		}
		return savingAccountHasInterestRate;
	}
	
	
	@Override
	public List<AccountHasInterestRate> getListAccountHasInterestRates(
			int startIndex, int maxReturn) {
		List<AccountHasInterestRate> accountHasInterestRates = null;
		/*AccountHasInterestRate accountHasInterestRate = null;
		String query = "SELECT * FROM `SavingAccount_has_InterestRate` WHERE `SavingAccount_has_InterestRate`.`SavingAccount_AccNumber`=?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, new Object[]{startIndex, maxReturn});
			accountHasInterestRates = new ArrayList<>();
			while(rs.next()){
				accountHasInterestRate = new SavingAccountHasInterestRate();
				accountHasInterestRate.setAccountNumber(rs.getInt("SavingAccount_AccNumber"));
				accountHasInterestRate.setInterestRateID(rs.getInt("InterestRate_InterestRateID"));
				accountHasInterestRate.setFromDate(rs.getDate("FromDate"));
				accountHasInterestRate.setBalance(rs.getFloat("Balance"));
				accountHasInterestRate.setToDate(rs.getDate("ToDate"));
				accountHasInterestRates.add(accountHasInterestRate);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeResultSetConnection(connection,
					preparedStatement, rs, logger);
		}*/
		return accountHasInterestRates;
	}

	@Override
	public int totalAccountHasInterestRates(int accountNumberID) {
		return getAllAccountHasInterestRates(accountNumberID).size();
	}

}
